package com.redteam.data.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;

/**
 * Class providing generic DAO methods.
 *
 * @author Jezreel Wilson
 * @author Steven Lawton
 * @version 1.1
 */
public abstract class AbstractDAO {

    /**
     * Returns a single <code>Object</code> from the database from its reference
     *
     * @param obj the <code>Object</code> to return
     * @return the single <code>Object</code> with matching ID (if it exists)
     * @throws SQLException
     */
    public Object getSingle(Object obj) throws SQLException {
        return getSingle(getObjectId(obj));
    }

    /**
     * Returns a single <code>Object</code> from the database with specified ID
     *
     * @param id ID of the <code>Object</code> to return
     * @return the single <code>Object</code> with matching ID (if it exists)
     * @throws SQLException
     */
    public Object getSingle(long id) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        Object obj = new Object();
        try {
            conn = getDS().getConnection();
            st = conn.createStatement();
            StringBuilder sb = new StringBuilder("SELECT * FROM ").append(this.getTableName()).append(" WHERE ID = ").append(id);
            rs = st.executeQuery(sb.toString());
            while (rs.next()) {
                obj = getObjectOut(rs);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AbstractDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(rs, st, conn);
            return obj;
        }
    }

    /**
     * Gets single <code>Object</code> specific to the descriminator
     * @param id
     * ID of the <code>Object</code> to return
     * @param discriminator
     * Discriminator of the <code>Object</code> to return
     * @return
     * the single <code>Object</code> with matching ID and discriminator(if it exists)
     * @throws SQLException
     */
    public Object getSingle(long id, String discriminator) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        Object obj = new Object();
        try {
            conn = getDS().getConnection();
            st = conn.createStatement();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT * FROM ").append(this.getTableName());
            if (discriminator.startsWith("IN")) {
                sb.append(" WHERE ID = ").append(id).append(" AND DISCRIMINATOR ").append(discriminator);
            } else {
                sb.append(" WHERE ID = ").append(id).append(" AND DISCRIMINATOR = '").append(discriminator).append("'");
            }
            rs = st.executeQuery(sb.toString());
            while (rs.next()) {
                obj = this.getObjectOut(rs);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AbstractDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(rs, st, conn);
        }
        return obj;
    }

    /**
     * Removes a row from the appropriate table in the database by its specified ID
     *
     * @param id the ID of the <code>Object</code> to remove from the database
     * @return number of rows affected (will be 0 or 1 in this method)
     * @throws SQLException
     */
    public int remove(long id) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        int result = 0;
        try {
            conn = this.getDS().getConnection();
            st = conn.createStatement();
            StringBuilder sb = new StringBuilder("DELETE FROM ").append(this.getTableName()).append(" WHERE ID= ").append(id);
            result = st.executeUpdate(sb.toString());
        } catch (SQLException ex) {
            Logger.getLogger(AbstractDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(rs, st, conn);
        }
        return result;
    }

    /**
     * Removes a row matching provided <code>Object</code> from the appropriate table in the database
     *
     * @param obj <code>Object</code> to remove from database
     * @return number of rows affected (will be 0 or 1)
     * @throws SQLException
     */
    public int remove(Object obj) throws SQLException {
        return remove(getObjectId(obj));
    }

    /**
     * Removes all <code>Object<code>s provided by child DAO from their appropriate table(s)
     *
     * @param objs all <code>Object<code>s to remove
     * @return number of rows affected by the remove operation
     * @throws SQLException
     */
    public int removeMany(List<Object> objs) throws SQLException {
        int rowsAffected = 0;
        for (Object o : objs) {
            rowsAffected += remove(o);
        }
        return rowsAffected;
    }

    /**
     * Updates the database with the attributes of the provided <code>Object</code>
     *
     * @param objectToUpdate the <code>Object</code> to update in the database
     * @return number of rows affected (0 or 1)
     */
    public int update(Object objectToUpdate) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        int rowsAffected = 0;
        try {
            conn = getDS().getConnection();
            st = conn.createStatement();
            rowsAffected = st.executeUpdate(buildUpdateQuery(objectToUpdate));
        } catch (SQLException sqle) {
            Logger.getLogger(AbstractDAO.class.getName()).log(Level.SEVERE, null, sqle);
        } finally {
            closeConnection(rs, st, conn);

        }
        return rowsAffected;
    }

    /**
     * Inserts specified <code>Object</code> into appropriate table in database
     *
     * @param obj the object to insert
     * @return number of rows affected (0 or 1 in this case)
     * @throws SQLException
     */
    public int insert(Object obj) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        int result = 0;

        try {
            conn = this.getDS().getConnection();
            st = conn.createStatement();
            result = st.executeUpdate(this.buildInsertQuery(obj));
        } catch (SQLException ex) {
            Logger.getLogger(AbstractDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(rs, st, conn);
        }
        return result;
    }

    /**
     * Returns all <code>Object</code>s in the database
     *
     * @return all <code>Object</code>s found
     * @throws SQLException
     */
    public List<Object> getAll() throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        List<Object> objectList = new ArrayList<Object>();
        try {
            conn = this.getDS().getConnection();
            st = conn.createStatement();
            StringBuilder sb = new StringBuilder("SELECT * FROM ").append(this.getTableName());
            rs = st.executeQuery(sb.toString());
            while (rs.next()) {
                Object object = this.getObjectOut(rs);
                objectList.add(object);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AbstractDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(rs, st, conn);
        }
        return objectList;
    }

    /**
     * Returns all <code>Object</code>s in the database
     *
     * @return all <code>Object</code>s found
     * @throws SQLException
     */
    public List<Object> getAll(String discriminator) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        List<Object> objectList = new ArrayList<Object>();
        try {
            conn = this.getDS().getConnection();
            st = conn.createStatement();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT * FROM ").append(this.getTableName());
            sb.append(" WHERE DISCRIMINATOR = '").append(discriminator).append("'");
            rs = st.executeQuery(sb.toString());
            while (rs.next()) {
                objectList.add(getObjectOut(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(AbstractDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(rs, st, conn);
        }
        return objectList;
    }

    /**
     * Inserts all Objects provided by child DAO into their appropriate table(s)
     *
     * @param objects
     * List of <code>Objects to insert into database
     * @return number of rows affected by the insert operation
     * @throws SQLException
     */
    public int insertMany(List<Object> objs) throws SQLException {
        int rowInserted = 0;
        for (Object o : objs) {
            rowInserted += insert(o);
        }
        return rowInserted;
    }

    /**
     * Closes the connection in use by the child DAO
     *
     * @param rs reference to <code>ResultSet</code> to close
     * @param st reference to <code>Statement</code> to close
     * @param conn reference to <code>Connection</code> to close
     */
    public void closeConnection(ResultSet rs, Statement st, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(AbstractDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Returns insert query for <code>Object</code> provided
     *
     * @param objectToInsert <code>Object</code> to insert into database
     * @return query to insert
     */
    public abstract String buildInsertQuery(Object obj);

    /**
     * Returns the name of the child DAO's table
     *
     * @return name of the child DAO's table
     */
    public abstract String getTableName();

    /**
     * Returns the <code>DataSource</code> from the child DAO
     *
     * @return the child DAO's <code>DataSource</code>
     */
    public abstract DataSource getDS();

    /**
     * Returns update query for <code>Object</code> provided
     *
     * @param objectToUpdate <code>Object</code> to update in the database
     * @return query to update
     */
    public abstract String buildUpdateQuery(Object o);

    /**
     * Returns a JavaBean from the provided <code>ResultSet</code>
     *
     * @param rs the <code>ResultSet</code> with which to build the bean
     * @return JavaBean constructed from <code>ResultSet</code>
     * @throws SQLException
     */
    public abstract Object getObjectOut(ResultSet rs) throws SQLException;

    /**
     * Returns the ID based on the <code>Object</code>
     *
     * @param o
     * @return id of specified <code>Object</code>
     */
    public abstract long getObjectId(Object o);
}
